import pymysql
import os


#修改sql文件
def adjustSQLFile(file_dir):
    for root, dirs, files in os.walk(file_dir):
        print(files)
        for name in files:
            r=open("E:\\work\\human\\sqlfile\\"+name,"r",encoding="utf-8")
            w=open("E:\\work\\human\\tmp\\"+name,"a",encoding="utf-8")
            lines=r.readlines()
            for i in range(len(lines)):
                if i == len(lines)-1:
                    w.write(lines[i].replace(",",")"))
                else:
                    w.write(lines[i])
            r.close()
            w.close()

def mergeSQLFile(file_dir):
    for root, dirs, files in os.walk(file_dir):
        w=open("E:\\work\\human\\sqlfile.txt","a",encoding="utf-8")
        for name in files:
            r=open("E:\\work\\human\\tmp\\"+name,"r",encoding="utf-8")
            for line in r:
                w.write(line)
            r.close()
        w.close()



#生成rec文件和sql文件
def getRECFileAndSQLFile():
    conn = pymysql.Connect(host = '10.170.128.56',
                           port = 3306,
                           user = 'root',
                           passwd = '123456',
                           db = 'crfd',
                           charset='utf8mb4')
    cur = conn.cursor()
    #获取指定库下所有表名
    # sql_tables="SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'cnki_humantity_simple'"
    # cur.execute(sql_tables)
    # tables=cur.fetchall();
    tables=['wx_person_copy_simple','wx_spe']
    for table in tables:
        #获取指定表中所有字段名
        #如果表名是通过SQL查出来的，table改为table[0]
        # sql_columns="select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.Columns where TABLE_SCHEMA = 'cnki_humantity_simple' and table_name='"+table[0]+"' order By ORDINAL_POSITION"
        sql_columns="select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.Columns where TABLE_SCHEMA = 'crfd' and table_name='"+table+"' order By ORDINAL_POSITION"
        cur.execute(sql_columns)
        result=cur.fetchall()

        e=open("E:\\work\\human\\sqlfile1\\"+table+".txt","a",encoding='utf-8')
        e.write(r'CREATE TABLE '+table.upper()+'\n')
        e.write(r"PATH '/home/bdms/bdmsdata_dev/" +table.upper()+"'"+'\n')
        e.write(r"AT HUMAN ALIASNAME '' ("+'\n')
        #遍历所有的字段,调整为rec标签后放入all集合
        all=[]
        for col in range(len(result)):
            column='<'+result[col][0]+'>='
            all.append(column)
            if result[col][1]=='int':
                e.write(result[col][0].upper()+" INTEGER(8) ASCII INDEX INTEGER NON,"+'\n')
            elif result[col][1]=='tinytext':
                e.write(result[col][0].upper()+" MTEXT(32767) ASCII INDEX STRCHAR NORMAL,"+'\n')
            elif result[col][1]=='varchar':
                e.write(result[col][0].upper()+" VCHAR(255) ASCII INDEX STRCHAR NORMAL,"+'\n')
            elif result[col][1]=='double':
                e.write(result[col][0].upper()+" INTEGER(8) ASCII INDEX INTEGER NON,"+'\n')
            elif result[col][1]=='mediumtext':
                e.write(result[col][0].upper()+" MTEXT(32767) ASCII INDEX STRCHAR NORMAL,"+'\n')
            elif result[col][1]=='text':
                e.write(result[col][0].upper()+" MTEXT(32767) ASCII INDEX STRCHAR NORMAL,"+'\n')
            elif result[col][1]=='tinyint':
                e.write(result[col][0].upper()+" INTEGER(8) ASCII INDEX NONE NON,"+'\n')
        print(len(all))
        #分页，每10万数据生成一个文件
        count_sql="select count(*) from `"+table+"`"
        cur.execute(count_sql)
        count=cur.fetchall()
        print(count[0][0])
        for n in range((count[0][0]//100000)+1):
            sql="select * from `"+table+"` limit "+str(n*100000)+",100000"
            cur.execute(sql)
            #datas每个表的数据集合
            datas=cur.fetchall()
            w=open("E:\\work\\human\\recfile1\\"+table+str(n)+".txt","a",encoding='gb18030')
            for data in datas:
                rec=[]
                rec.append('<REC>')
                for i in range(len(all)):
                    if data[i]:
                        rec.append(all[i]+str(data[i]))
                rec='\n'.join(rec)
                w.write(rec+'\n')
        w.close()
        e.close()
    cur.close()
    conn.close()


if __name__ == '__main__':

    getRECFileAndSQLFile()